﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Data;
using System.Collections;

namespace ResultsProducer
{

    class awardCategory
    {
        public string description { get; set; }
        public int minAge { get; set; }
        public int maxAge { get; set; }
        
        public awardCategory(string desc, int min, int max)
        {
            description = desc;
            minAge = min;
            maxAge = max;
        }
    }

    class Program
    {
        static SqlConnection cn;
        static void Main(string[] args)
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["HuberRace"].ConnectionString);
            cn.Open();

            // Produce awards sheet for Stumptown race. This is the sheet
            // given to the RD to announce awards.
            var sw = new StreamWriter("StompAwards.htm");
            produceAwardsSheetForRace(43, sw, "Stomp Out Abuse 5k", "Male", false, "");
            //produceAwardsSheetForRace(45, sw, "Stomp Out Abuse 5k", "Female", false, "");
            sw.Close();

            /*
            sw = new StreamWriter("HappyValleyAwardsChips.htm");
            produceAwardsSheetForRace(43, sw, "Happy Valley Fun Run 5k", "Male", false, "5k", "ResultsWithAGChips");
            produceAwardsSheetForRace(43, sw, "Happy Valley Fun Run 5k", "Female", false, "5k", "ResultsWithAGChips");
            produceAwardsSheetForRace(43, sw, "Happy Valley Fun Run 10k", "Male", false, "10k", "ResultsWithAGChips");
            produceAwardsSheetForRace(43, sw, "Happy Valley Fun Run 10k", "Female", false, "10k", "ResultsWithAGChips");
            sw.Close();
            */

            // Product results table. This is for:
            // 1) Post online post race.
            // 2) Printing and posting during the race.
            produceResultsForUSATF(45, 3.1F, "Stop5kCSV.csv", "2013 Stomp 5k.htm", "Stomp5kHeader.htm", "DefaultFooter.htm", "ResultsWithAG", null);

            /*
            produceResultsForUSATF(43, 6.2F, "HappyValley10kForRogueChip.csv", "2013 Happy Valley 10kChip.htm", "HappyValleyHeader10k.htm", "DefaultFooter.htm", "ResultsWithAGChips", "10k");
            produceResultsForUSATF(43, 3.1F, "HappyValley5kForRogueChip.csv", "2013 Happy Valley 5kChip.htm", "HappyValleyHeader5k.htm", "DefaultFooter.htm", "ResultsWithAGChips", "5k");
            */
        }

        /// <summary>
        /// Produce individual resutls in HTML, intended for postingoline, and CSV, intended to
        /// be sent to AP.
        /// </summary>
        /// <param name="raceId">Id of race results are for</param>
        /// <param name="distance">Distance of race</param>
        /// <param name="CSVFilename">CSV Filename</param>
        /// <param name="HTMLFilename">HTML Filename</param>
        /// <param name="HTMLHeaderFilename">HTML Header. Can include links and proper stylesheets</param>
        /// <param name="resultsViewName">Results tablename</param>
        private static void produceResultsForUSATF(int? raceId, float distance, string CSVFilename, string HTMLFilename, string HTMLHeaderFilename, string HTMLFooterFilename, 
            string resultsViewName = "ResultsWithAGForUSATF",
            string raceName =  null)
        {
            var swCSV = new StreamWriter(CSVFilename);
            List<string> csvFields = new List<string>();
            csvFields.Add("Bib");            
            csvFields.Add("Place");
            csvFields.Add("FName");
            csvFields.Add("LName");
            csvFields.Add("Age");
            csvFields.Add("Gender");
            csvFields.Add("AgeGroup");
            csvFields.Add("AgeGroupPlace");
            csvFields.Add("AgeGroupTotal");
            csvFields.Add("GenderPlace");
            csvFields.Add("GenderTotal");
            csvFields.Add("ChipTime");
            csvFields.Add("Pace");
            swCSV.WriteLine(string.Join(",", csvFields));

            var fieldListForHTML =new Dictionary<string,string>();
            fieldListForHTML.Add("Place", "place");
            fieldListForHTML.Add("Time", "displayTime");
            fieldListForHTML.Add("Pace", "displayPace");
            fieldListForHTML.Add("Bib", "bib");
            fieldListForHTML.Add("Name", "name");
            fieldListForHTML.Add("Age", "age");
            fieldListForHTML.Add("City", "city");
            fieldListForHTML.Add("State", "state");
            fieldListForHTML.Add("Gender", "Gender");
            fieldListForHTML.Add("Gender Pl", "GenderPlace");
            fieldListForHTML.Add("Age Group", "AG");
            fieldListForHTML.Add("Age Group Pl", "AG Place");
            //fieldListForHTML.Add("Team", "team");
            //fieldListForHTML.Add("Team Pl", "points");
            //fieldListForHTML.Add("USATF Team Pl", "pointsUSATF");

            var swHTML = new StreamWriter(HTMLFilename);
            var srHTMLHeader = new StreamReader(HTMLHeaderFilename);
            swHTML.Write(srHTMLHeader.ReadToEnd());
            srHTMLHeader.Close();
            swHTML.WriteLine("<a name=individual></a>");
            swHTML.WriteLine(@"<table class='table table-striped table-bordered table-condensed' id='individualResults'>");
            swHTML.WriteLine("<thead><tr class='header'>");
            foreach (var field in fieldListForHTML)
            {
                swHTML.Write("<th>{0}</th>", field.Key);   
            }
            swHTML.WriteLine("</tr></thead></tbody>");


            
            var scoringTeams = scoringTeamsTable(raceId, false);
            var scoringTeamsUSATF = scoringTeamsTable(raceId, true);

            var cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.Parameters.AddWithValue("@raceId", raceId);
            cmd.CommandText = @"
                    SELECT 
                        r.*,UPPER(p.city) as city,LEFT(UPPER(p.state),2) as state
                    FROM " + resultsViewName + @" r
                    INNER JOIN Participants p ON
                        p.bib = r.bib
                    ";
            if (raceId != null)
            {
                cmd.CommandText += @"
                    WHERE 
                        raceId = @raceId";
            }

            if (raceName != null)
            {
                cmd.CommandText += @"
                    AND p.raceName = @raceName";
                cmd.Parameters.AddWithValue("@raceName", raceName);
            }
            cmd.CommandText += @"
                    ORDER BY place";
            cmd.Connection = cn;
            
            var rows = cmd.ExecuteReader();
            int i = 1;

            // These four variables keep track of the next score of the four competitions.
            // I should use something better than variable eh?
            int FOpen = 0;
            int FMasters = 0;
            int FOpenUSATF = 0;
            int FMastersUSATF = 0;

            int MOpen = 0;
            int MMasters = 0;
            int MOpenUSATF = 0;
            int MMastersUSATF = 0;


            while (rows.Read())
            {
                TimeSpan ts = TimeSpan.Parse(rows["time"].ToString());
                string displayTime = String.Format("{0:0}:{1:00}:{2:00}",
                    ts.Hours, ts.Minutes, ts.Seconds);
                
                TimeSpan pace = new TimeSpan((long)(ts.Ticks / distance));
                string displayPace = String.Format("{0:0}:{1:00}:{2:00}",
                    pace.Hours, pace.Minutes, pace.Seconds);

                var name = rows["name"].ToString().Trim();
                var fname = name.Substring(0, name.LastIndexOf(" ")).Trim();
                var lname = name.Substring(name.LastIndexOf(" ") + 1).Trim();

                //swCSV.WriteLine(rows["place"] + "," + displayTime + "," + displayPace + "," + fname + "," + lname + "," + rows["age"] + "," + rows["city"] + "," + rows["state"] + "," + rows["gender"] + "," + rows["GenderPlace"] + "," + rows["AG Place"] +","+rows["team"]+","+rows["TeamDivision"]);
                csvFields = new List<string>();
                csvFields.Add(rows["bib"].ToString());
                csvFields.Add(rows["place"].ToString());
                csvFields.Add(fname);
                csvFields.Add(lname);
                csvFields.Add(rows["age"].ToString());
                csvFields.Add(rows["gender"].ToString());
                csvFields.Add(rows["AG"].ToString());
                csvFields.Add(rows["AG Place"].ToString());
                csvFields.Add(rows["Total AG"].ToString());
                csvFields.Add(rows["GenderPlace"].ToString());
                csvFields.Add(rows["Total Gender"].ToString());
                csvFields.Add(displayTime);
                csvFields.Add(displayPace);
                swCSV.WriteLine(string.Join(",", csvFields));

                string rowClass;
                if (i % 2 == 0)
                    rowClass = "even";
                else
                    rowClass = "odd";
                i++;

                int? points = null;
                int? pointsUSATF = null;

                bool scoredTeamPoints = false;
                bool scoredTeamPointsUSATF = false;

                //points = calculateTeamPoints(scoringTeams, rows, out scoredTeamPoints, ref MMasters, ref MOpen, ref FOpen, ref FMasters, false);
                //pointsUSATF = calculateTeamPoints(scoringTeamsUSATF, rows, out scoredTeamPointsUSATF, ref MMastersUSATF, ref MOpenUSATF, ref FOpenUSATF, ref FMastersUSATF, true);

                swHTML.WriteLine("<tr class='{0}'>", rowClass);
                foreach (var field in fieldListForHTML)
                {
                    string value;
                    switch (field.Value)
                    {
                        case "displayTime":
                            value = displayTime;
                            break;
                        
                        case "displayPace":
                            value = displayPace;
                            break;

                        case "name":
                            value = name;
                            break;

                        case "fname":
                            value = fname;
                            break;

                        case "lname":
                            value = lname;
                            break;

                        //case "points":
                        //    if (scoredTeamPoints)
                        //        value = points.ToString();
                        //    else
                        //        value = "";
                        //    break;

                        //case "pointsUSATF":
                        //    if (scoredTeamPointsUSATF)
                        //        value = pointsUSATF.ToString();
                        //    else
                        //        value = "";
                        //    break;

                        default:
                            value = rows[field.Value].ToString();
                            break;
                    }

                    swHTML.Write("<td>{0}</td>", value.ToUpper());
                }
                swHTML.WriteLine("</tr>");
           
            }
            swHTML.WriteLine("</tbody></table></div></div>");
            
            /*
            swHTML.WriteLine("<a name=team></a>");
            swHTML.WriteLine("<div class='row-fluid'><div class='span12'>");

            if (raceId == 38)
            {
                writeTeamResultsHTML(swHTML, scoringTeams, 'M', "Open", false);
                writeTeamResultsHTML(swHTML, scoringTeamsUSATF, 'M', "Open", true);

                writeTeamResultsHTML(swHTML, scoringTeams, 'M', "Masters", false);
                writeTeamResultsHTML(swHTML, scoringTeamsUSATF, 'M', "Masters", true);
            }
            else if (raceId == 39)
            {
                writeTeamResultsHTML(swHTML, scoringTeams, 'F', "Open", false);
                writeTeamResultsHTML(swHTML, scoringTeamsUSATF, 'F', "Open", true);

                writeTeamResultsHTML(swHTML, scoringTeams, 'F', "Masters", false);
                writeTeamResultsHTML(swHTML, scoringTeamsUSATF, 'F', "Masters", true);
            }
            */

            var srHTMLFooter = new StreamReader(HTMLFooterFilename);
            swHTML.Write(srHTMLFooter.ReadToEnd());

            rows.Close();
            swCSV.Close();
            swHTML.Close();
        }

        private static int? calculateTeamPoints(DataTable scoringTeams, SqlDataReader rows, out bool scoredTeamPoints,
                                                ref int MMasters, ref int MOpen, ref int FOpen, ref int FMasters, bool isUSATF)
        {
            var division = rows["TeamDivision"].ToString();
            if (isUSATF)
            {
                division = rows["USATFTeamDivision"].ToString();
            }

            scoredTeamPoints = false;
            int? points = 0;
            if (!String.IsNullOrEmpty(division))
            {
                // Get the points for this person and update the next score of the 
                // for this competition.
                string gender = rows["gender"].ToString();
                if (gender[0] == 'M')
                {
                    if (division == "MASTERS")
                    {
                        MMasters++;
                        points = MMasters;
                    }
                    else
                    {
                        MOpen++;
                        points = MOpen;
                    }
                }
                else
                {
                    if (division == "MASTERS")
                    {
                        FMasters++;
                        points = FMasters;
                    }
                    else
                    {
                        FOpen++;
                        points = FOpen;
                    }
                }

                string team = rows["team"].ToString();

                // Get current score for team
                DataRow[] scoringRow = scoringTeams.Select("Gender = '" + gender + "' AND team = '" + team + "'");
                if (scoringRow.Length != 1)
                    throw new Exception("Could not find row for Gender = '" + gender + "' AND team = '" + team +
                                        "' in scoringTeams");

                int teamPlace;
                teamPlace = (int) scoringRow[0]["PlaceRecorded"] + 1;
                scoringRow[0]["PlaceRecorded"] = teamPlace;
                if (teamPlace <= 4)
                {
                    scoredTeamPoints = true;
                    int currentScore;
                    currentScore = (int) scoringRow[0]["Score"];
                    scoringRow[0]["Score"] = points + currentScore;
                    string details = rows["name"].ToString() + " (" + points + ")";
                    if (!String.IsNullOrEmpty(scoringRow[0]["ScoreDetails"].ToString()))
                    {
                        details = ", " + details;
                    }
                    scoringRow[0]["ScoreDetails"] = scoringRow[0]["ScoreDetails"].ToString() + details;
                }
                else if (teamPlace == 5)
                {
                    scoringRow[0]["5thRunnerPoints"] = points;
                }
            }
            return points;
        }

        private static DataTable scoringTeamsTable(int? raceId, bool isUSATF)
        {
            var USATFSQL = "";
            if (isUSATF)
            {
                USATFSQL = "AND isUSATF = 1";
            }

        // Create data structure for scoring. In the data structure we track
            // Team, Team Division (Male Masters Open, Male Open, Female Masters Open, Female Open), Score, 5thRunningPlace, LastPlaceRecorded
            var scoringTeams = new DataTable();
            var cmd = new SqlCommand();
            cmd.CommandText = @"
                SELECT 
                    UPPER(Team) AS Team, Gender, (CASE WHEN team LIKE '%masters%' THEN 'MASTERS' ELSE 'OPEN' END) AS TeamDivision
                FROM [ResultsWithUSATF]
                WHERE 
                    raceId = @raceId
                    AND ISNULL(Team,'') <> '' " + USATFSQL + @"
                GROUP BY Team, Gender, (CASE WHEN team LIKE '%masters%' THEN 'MASTERS' ELSE 'OPEN' END)
                HAVING COUNT(*) >= 4";
            cmd.Connection = cn;
            cmd.Parameters.AddWithValue("@raceId", raceId);
            scoringTeams.Load(cmd.ExecuteReader());

            var scoreColumn = new DataColumn("Score", typeof (int));
            scoreColumn.DefaultValue = 0;
            scoringTeams.Columns.Add(scoreColumn);

            var FifthRunnerPointsCol = new DataColumn("5thRunnerPoints", typeof (int)); // Rename to 5thRunningPlace
            FifthRunnerPointsCol.DefaultValue = 1000000;
                // If there's not a 5th runners this huge value forces a team that ties on four points to win. It also means a race could never have more than a million participants.
            // See if we can do this in cleaner way.
            scoringTeams.Columns.Add(FifthRunnerPointsCol);

            var placeRecordedColumn = new DataColumn("PlaceRecorded", typeof (int)); // Rename to LastPlaceRecorded
            placeRecordedColumn.DefaultValue = 0;
            scoringTeams.Columns.Add(placeRecordedColumn);

            var scoreDetailsColumn = new DataColumn("ScoreDetails", typeof (string));
                // List in a text format each runner that was used in calculating 
            // the score.
            scoringTeams.Columns.Add(scoreDetailsColumn);
            return scoringTeams;
        }

        private static void writeTeamResultsHTML(StreamWriter swHTML, DataTable scoringTeams, char gender, string teamDivision, bool isUSATF)
        {
            string teamPrefix;
            if (isUSATF)
                teamPrefix = "USATF";
            else
                teamPrefix = "Stumptown";
            
            string displayGender;
            if (gender == 'M')
                displayGender = "Male";
            else
                displayGender = "Female";
            
            swHTML.WriteLine(@"
<h2>"+teamPrefix+@" Team "+displayGender+ " " + teamDivision + @"</h2>
<table class='table table-striped table-bordered table-condensed'>
<thead><tr class='header'>
<td>Place</td>
<td>Team</td>
<td>Score</td>
<td>Scoring Runners (In parenthesis are the place the runner scored)</td>
</tr></thead><tbody>");
            DataRow[] resultsRows = scoringTeams.Select("Gender = '"+ gender +"' AND TeamDivision = '"+ teamDivision +"'", "Score, 5thRunnerPoints");
            int place = 1;
            foreach (DataRow r in resultsRows)
            {
                swHTML.WriteLine("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>", place, r["team"], r["Score"], r["ScoreDetails"]);
                place++;
            }
            swHTML.WriteLine("</tbody></table>");
        }
        

        private static void produceAwardsSheetForRace(int raceId, StreamWriter sw, string awardsFor, string gender, bool USATFResults, string raceName = null, string resultsTableName = "ResultsWithAG")
        {
            sw.WriteLine(@"<html>
<head>
<title>" + awardsFor + @"</title>
<link rel='Stylesheet' type='text/css' href='css/bootstrap.min.css' />
</head>
<body>");
            string USATFSQL = "";
            if (USATFResults)
            {
                USATFSQL = "AND isUSATF = 1";
            }

            // Overall results male
            var cmd = new SqlCommand(@"
                SELECT TOP 3 '*MALE OVERALL*' as award,time,name,age,gender,AG,GenderPlace,bib,place
                FROM "+resultsTableName +@" 
                WHERE 
                    raceId = @raceId
                    AND gender = @gender " + USATFSQL +@"
                ORDER BY place", cn);
            cmd.Parameters.AddWithValue("@raceId", raceId);
            cmd.Parameters.AddWithValue("@gender", 'm');
            createResultsTableFromCmd(awardsFor, "Male Overall", "", sw, cmd);

            // Overall results female
            cmd = new SqlCommand(@"
                SELECT TOP 3 '*FEMLAE OVERALL*' as award,time,name,age,gender,AG,GenderPlace,bib,place
                FROM " + resultsTableName + @" 
                WHERE 
                    raceId = @raceId
                    AND gender = @gender " + USATFSQL + @"
                ORDER BY place", cn);
            cmd.Parameters.AddWithValue("@raceId", raceId);
            cmd.Parameters.AddWithValue("@gender", 'f');
            createResultsTableFromCmd(awardsFor, "Female Overall", "", sw, cmd);

            List<awardCategory> awards = new List<awardCategory>();
            awards.Add( new awardCategory("Under 20", 1, 20) );
            awards.Add(new awardCategory("21-29", 21, 29));
            awards.Add(new awardCategory("30-39", 30, 39));
            awards.Add(new awardCategory("40-49", 40, 49));
            awards.Add(new awardCategory("50-59", 50, 59));
            awards.Add(new awardCategory("60+", 60, 100));
            foreach (awardCategory a in awards)
            {
                cmd = new SqlCommand(@"
                SELECT TOP 3 '*FEMLAE OVERALL*' as award,time,name,age,gender,AG,GenderPlace,bib,place
                FROM " + resultsTableName + @" 
                WHERE 
                    raceId = @raceId
                    AND age BETWEEN @minAge AND @maxAge
                    AND place NOT IN (1,2,3)                    
                ORDER BY place", cn);
                cmd.Parameters.AddWithValue("@raceId", raceId);
                cmd.Parameters.AddWithValue("@minAge", a.minAge);
                cmd.Parameters.AddWithValue("@maxAge", a.maxAge);
                createResultsTableFromCmd(awardsFor, a.description, "Non Gendered", sw, cmd);                
            }


/*
            // Masters results
            cmd = new SqlCommand(@"
                SELECT TOP 3 '*MALE MASTERS*' as award,time,name,age,gender,AG,GenderPlace,bib,place
                FROM " + resultsTableName + @" 
                WHERE 
                    raceId =  @raceId
                    AND raceName = @raceName
                    AND age >= 40
                    AND gender = @gender " + USATFSQL + @"
                    AND bib NOT IN 
                        (SELECT TOP 3 bib FROM ResultsWithAG
                        WHERE raceId =  @raceId
                        AND gender = @gender " + USATFSQL + @"
                        ORDER BY place)
                ORDER BY place", cn);
            cmd.Parameters.AddWithValue("@raceId", raceId);
            cmd.Parameters.AddWithValue("@gender", gender[0]);
            cmd.Parameters.AddWithValue("@raceName", raceName);
            //createResultsTableFromCmd(awardsFor, "MASTERS", gender, sw, cmd);

            // Youth results
            cmd = new SqlCommand(@"
                SELECT TOP 3 '*UNDER 18*' as award,time,name,age,gender,AG,GenderPlace,bib,place
                FROM " + resultsTableName + @" 
                WHERE 
                    raceId = @raceId
                    AND raceName = @raceName
                    AND age < 18
                    AND age > 0
                    AND gender = @gender " + USATFSQL + @"
                    AND bib NOT IN 
                        (SELECT TOP 3 bib FROM ResultsWithAG
                        WHERE raceId =  @raceId
                        AND gender = @gender " + USATFSQL + @" 
                        ORDER BY place)
                ORDER BY place", cn);
            cmd.Parameters.AddWithValue("@raceId", raceId);
            cmd.Parameters.AddWithValue("@gender", gender[0]);
            cmd.Parameters.AddWithValue("@raceName", raceName);
            //createResultsTableFromCmd(awardsFor, "YOUTH", gender, sw, cmd);
 * */
        }

        private static void createResultsTableFromCmd(string awardsFor, string divsionName, string gender, StreamWriter sw, SqlCommand cmd)
        {
            var rows = cmd.ExecuteReader();
            sw.WriteLine(@"
<h2>" + awardsFor + " " + gender + " " + divsionName + @"</h2>
<table class='table table-striped table-bordered table-condensed'>
<tr>
    <td>Award</td>
    <td>Name</td>
    <td>Time</td>
    <td>Age</td>
    <td>Bib</td>
    <td>Gender</td>
</tr>");
            string[] prettyNames = { "FIRST", "SECOND", "THIRD" };
            int i = 0;
            while (rows.Read())
            {
                sw.WriteLine(@"<tr><td>" + prettyNames[i] + " " + divsionName + "</td><td>" + rows["name"] + @"</td>
                    <td>" + rows["time"] + @"</td>
                    <td>" + rows["age"] + @"</td>
                    <td>" + rows["bib"] + @"</td>
                    <td>" + rows["gender"] + "</td></tr>");
                i++;
            }
            rows.Close();
            sw.WriteLine("</table>");
        }
    }
}
